﻿Imports System.Data.SqlClient

<ComClass(ClsSqHelper.ClassId, ClsSqHelper.InterfaceId, ClsSqHelper.EventsId)>
Public Class ClsSqHelper

#Region "COM GUID"
    ' 这些 GUID 提供此类的 COM 标识 
    ' 及其 COM 接口。若更改它们，则现有的
    ' 客户端将不再能访问此类。
    Public Const ClassId As String = "d32df843-039f-406e-a410-309b4e951324"
    Public Const InterfaceId As String = "d007f319-aeda-4b59-8eee-40fd3e6268a3"
    Public Const EventsId As String = "4779eb13-4ac6-4b40-ac17-5a41b2d4a576"
#End Region

    ' 可创建的 COM 类必须具有一个不带参数的 Public Sub New() 
    ' 否则， 将不会在 
    ' COM 注册表中注册此类，且无法通过
    ' CreateObject 创建此类。
    Public Sub New()
        MyBase.New()
    End Sub

#Region "com 可见的"

#End Region
    Private _conStr As String
    Public Property ConString() As String
        Get
            Return _conStr
        End Get
        Set(ByVal value As String)
            _conStr = value
        End Set
    End Property

    ''' <summary>
    ''' 获得查询的数据
    ''' </summary>
    ''' <param name="strSelectCmd"></param>
    ''' <returns></returns>
    Public Function ExcuteDataReader(ByVal strSelectCmd As String) As Object()
        Dim conn As SqlConnection = Nothing
        Dim sdr As SqlDataReader
        Try
            conn = New SqlConnection(Me.ConString)
            Dim cmd As New SqlCommand(strSelectCmd, conn)
            conn.Open()
            sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
        Catch ex As Exception
            conn.Dispose()
            Throw ex
        End Try

        Dim rtn As New List(Of Object), i As Integer = 0

        If sdr.HasRows Then
            While sdr.Read()
                Dim fileds(0 To sdr.FieldCount - 1) As String '字段
                Dim Values(0 To sdr.FieldCount - 1) As String '值
                If i = 0 Then
                    For index = 0 To sdr.FieldCount - 1
                        fileds(index) = sdr.GetName(index)
                    Next
                    rtn.Add(fileds)
                Else
                    For index = 0 To sdr.FieldCount - 1
                        Values(index) = If(IsDBNull(sdr.GetValue(index)), "N.A.", sdr.GetValue(index).ToString())
                    Next
                    rtn.Add(Values)
                End If
                i += 1
            End While
        End If
        Return rtn.ToArray()

    End Function

    ''' <summary>
    ''' 返回受影响的函数
    ''' </summary>
    ''' <param name="strCmd"></param>
    ''' <returns></returns>
    Public Function ExcuteNonQuery(ByVal strCmd As String) As Integer
        Using conn As SqlConnection = New SqlConnection(Me.ConString)
            Dim cmd As New SqlCommand(strCmd, conn)
            conn.Open()
            Return cmd.ExecuteNonQuery
        End Using
    End Function

End Class


